<?php
/*
 * This page creates the functional
 * interface for storing session data
 * in a database
 * This page also starts the session
 */

/*
 * database table
 * CREATE TABLE sessions(id CHAR(32) NOT NULL, data TEXT, last_accessed TIMESTAMP NOT NULL, PRIMARY KEY (id));
 */

$sdbc = null;

function open_session(){
    global $sdbc;
    $sdbc = mysql_connect('localhost', 'username','passwd') or die('cannot connect to the database.');
    $sd = mysql_select_db('test');
    return true;
}

function close_session(){
    global $sdbc;
    return mysql_close($sdbc);
}

function read_session($sid){
    global $sdbc;
    $q = sprintf('SELECT data FROM sessions WHERE id="%s"', mysql_real_escape_string($sid));
    $r = mysql_query($q);
    if(mysql_num_rows($r) == 1){
        list($data) = mysql_fetch_array($r, MYSQLI_NUM);
        return $data;
    }else{
        return '';
    }
}

function write_session($sid, $data){
    global $sdbc;
    $q = sprintf('REPLACE INTO sessions(id, data) VALUES("%s", "%s")',
        mysql_real_escape_string($sid),
        mysql_real_escape_string($data) 
    );

    $r = mysql_query($q);
    return mysql_affected_rows($sdbc);
}

function destory_session($sid){
    global $sdbc;
    $q = sprintf('DELETE FROM sessions WHERE id="%s"', mysql_real_escape_string($sid));
    $r = mysql_query($q);
    $_SESSION = array();
    return mysql_affected_rows($sdbc);
}

function clean_session($expire){
    global $sdbc;
    $q = sprintf('DELETE FROM sessions WHERE DATE_ADD(last_accessed, INTERVAL %d SECOND) < NOW()', (int)$expire);
    $r = mysql_query($q);
    return mysql_affected_rows($sdbc);
}


session_set_save_handler('open_session', 'close_session', 'read_session', 'write_session', 'destory_session', 'clean_session');
session_start();
?>
<!-- test case page-->
<?php require_once 'db_session.php';?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head> 
<meta http-equiv="content-type" content="text/html;charset=utf-8"/> 
<title>DB Session Test</title>
</head>
<body>
<?php 
    if(empty($_SESSION)){
        $_SESSION['blah'] = 'umlaut';
        $_SESSION['this'] = 3615684.45;
        $_SESSION['that'] = 'blue';
        echo '<p>Session data stored.</p>';
    }else{
        echo '<p>Session data exists:<pre>' . print_r($_SESSION). '</pre></p>'; 
    }

    if(isset($_GET['logout'])){
        session_destroy();
        echo '<p>Session destroyed</p>';
    }else{
        echo '<a href="session.php?logout=true">Log Out</a>';
    }
    echo '<p>Session data:<pre>' . print_r($_SESSION). '</pre></p>'; 
?>
</body>
</html>
<?php session_write_close(); ?>
